package com.example.demo.utils;

import com.example.demo.entity.*;
import com.example.demo.mapper.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Component
public class JDBCUtil {

    @Autowired
    ActivityMapper activityMapper;
    @Autowired
    DeptMapper deptMapper;
    @Autowired
    PeopleMapper peopleMapper;
    @Autowired
    CompanyMapper companyMapper;
    @Autowired
    AreaMapper areaMapper;

    public static JDBCUtil jdbcUtil;

    @PostConstruct
    public void init() {
        jdbcUtil = this;
    }

    public static void getConnection() throws Exception{ // 建立返回值为Connection的方法
        Connection connection;

        try { // 加载数据库驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String user = "gh001";//数据库登录名
        String password = "zjgh2023";//密码
        try {
            connection = DriverManager.getConnection("jdbc:mysql://172.16.40.110:3306/contractual_service?useUnicode=true&characterEncoding=gbk", user, password);
            System.out.println("数据库连接成功");
            Statement statement = connection.createStatement();
            String sql = "SELECT * FROM quotation;";
            ResultSet resultSet = statement.executeQuery(sql);

            while(resultSet.next()){
                String id = resultSet.getString("id");
                Date date = new Date();
                SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd :hh:mm:ss");
                BdActivity bdActivity1 = jdbcUtil.activityMapper.selectByQuatationId(id);

                if(bdActivity1 == null){
                    BdActivity bdActivity = new BdActivity();
                    bdActivity.setQuatationId(id);
                    bdActivity.setName(resultSet.getString("con_name"));
                    bdActivity.setStartDate(transferFormat(resultSet.getString("con_start_date")));
                    bdActivity.setEndDate(transferFormat(resultSet.getString("con_end_date")));
                    bdActivity.setGuestCount(resultSet.getInt("con_user_count"));
                    bdActivity.setGuestName(resultSet.getString("unit_name"));
                    bdActivity.setIsActive(resultSet.getBoolean("is_active"));
                    if (dateFormat.format(date).compareTo(transferFormat(resultSet.getString("con_start_date")))<0){
                        bdActivity.setStatus("准备中");
                    }else if(dateFormat.format(date).compareTo(transferFormat(resultSet.getString("con_start_date")))>=0
                            &&dateFormat.format(date).compareTo(transferFormat(resultSet.getString("con_end_date")))<=0){
                        bdActivity.setStatus("进行中");
                    }else {
                        bdActivity.setStatus("已结束");
                    }
                    jdbcUtil.activityMapper.insert(bdActivity);
                }
                else{
                    bdActivity1.setName(resultSet.getString("con_name"));
                    bdActivity1.setStartDate(transferFormat(resultSet.getString("con_start_date")));
                    bdActivity1.setEndDate(transferFormat(resultSet.getString("con_end_date")));
                    bdActivity1.setGuestCount(resultSet.getInt("con_user_count"));
                    bdActivity1.setGuestName(resultSet.getString("unit_name"));
                    bdActivity1.setIsActive(resultSet.getBoolean("is_active"));
                    if (dateFormat.format(date).compareTo(transferFormat(resultSet.getString("con_start_date")))<0){
                        bdActivity1.setStatus("准备中");
                    }else if(dateFormat.format(date).compareTo(transferFormat(resultSet.getString("con_start_date")))>=0
                            &&dateFormat.format(date).compareTo(transferFormat(resultSet.getString("con_end_date")))<=0){
                        bdActivity1.setStatus("进行中");
                    }else {
                        bdActivity1.setStatus("已结束");
                    }
                    jdbcUtil.activityMapper.updateById(bdActivity1);
                }
            }
            resultSet.close();
            statement.close();


            List<BdActivity> activities = jdbcUtil.activityMapper.selectList(null);
            PreparedStatement preparedStatement = null;
            ResultSet resultSet1 = null;
            String sql1 = "SELECT * FROM quotationvenueassociation WHERE is_active = 1 and quotation_id = ?;";
            for(BdActivity item : activities){
                preparedStatement = connection.prepareStatement(sql1, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                preparedStatement.setString(1, item.getQuatationId());
                resultSet1 = preparedStatement.executeQuery();
                if(resultSet1.first()){
//                    System.out.println("venue: " + resultSet1.getString("venue_name"));
                    item.setVenue(resultSet1.getString("venue_name"));
                    jdbcUtil.activityMapper.updateById(item);
                }
            }
            resultSet1.close();
            preparedStatement.close();
            connection.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    public static void getDepartment(){
//        Connection connection;
//        String user = "gh001";  //数据库登录名
//        String password = "zjgh2023";  //密码
//
//        try{
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            connection = DriverManager.getConnection("jdbc:mysql://172.16.40.110:3306/basic_service?useUnicode=true&characterEncoding=gbk", user, password);
//            String sql = "SELECT * FROM organizationnode;";
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            ResultSet resultSet = preparedStatement.executeQuery();
//
//            while(resultSet.next()){
//                SysDept sysDept1 = jdbcUtil.deptMapper.selectByOutId(resultSet.getString("id"));
//                if(sysDept1 == null){
//                    SysDept sysDept = new SysDept();
//                    sysDept.setOutId(resultSet.getString("id"));
//                    sysDept.setName(resultSet.getString("name"));
//                    sysDept.setType(resultSet.getString("node_type"));
//                    sysDept.setParentId(resultSet.getString("parent_id"));
//                    sysDept.setOrigin("场馆运营系统");
//                    sysDept.setIsActive(resultSet.getBoolean("is_active"));
//                    sysDept1.setType(resultSet.getString("node_type"));
//                    jdbcUtil.deptMapper.insert(sysDept);
//                }
//                SysCompany sysCompany1 = jdbcUtil.companyMapper.selectById(resultSet.getString("id"));
//                if(sysCompany1 == null){
//                    SysCompany sysCompany = new SysCompany();
//                    sysCompany.setId(resultSet.getInt("id"));
//                    sysCompany.setName(resultSet.getString("name"));
//                    jdbcUtil.companyMapper.insert(sysCompany);
//                }
//            }
//            resultSet.close();
//            preparedStatement.close();
//            connection.close();
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//
//        List<SysDept> sysDepts = jdbcUtil.deptMapper.getAllDepartments();
//        for(SysDept item : sysDepts){
//            if(item.getParentId() != null){
//                SysDept sysDept = jdbcUtil.deptMapper.selectByOutId(item.getParentId());
//                item.setParent(sysDept.getName());
//            }
//        }
//    }

//    public static void getPeople(){
//        Connection connection;
//        String user = "gh001";  //数据库登录名
//        String password = "zjgh2023";  //密码
//
//        try{
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            connection = DriverManager.getConnection("jdbc:mysql://172.16.40.110:3306/basic_service?useUnicode=true&characterEncoding=gbk", user, password);
//            String sql = "SELECT * FROM organizationnodeuser;";
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            ResultSet resultSet = preparedStatement.executeQuery();
//
//            PreparedStatement preparedStatement1 = null;
//            ResultSet resultSet1 = null;
//
//            while(resultSet.next()){
//                String id = resultSet.getString("user_id");
//                SysPeople sysPeople1 = jdbcUtil.peopleMapper.selectByOutId(id);
//                if(sysPeople1 == null){
//                    SysPeople sysPeople = new SysPeople();
//                    String sql1 = "SELECT * FROM user WHERE is_active = 1 and id = ?;";
//                    preparedStatement1 = connection.prepareStatement(sql1, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//                    preparedStatement1.setString(1, id);
//                    resultSet1 = preparedStatement1.executeQuery();
//                    if(resultSet1.first()){
//                        sysPeople.setName(resultSet1.getString("name"));
//                        sysPeople.setPhone(resultSet1.getString("mobile"));
//                        sysPeople.setUsername(resultSet1.getString("mobile"));
//                        sysPeople.setIsActive(resultSet1.getBoolean("is_active"));
//                    }
//                    sysPeople.setOutId(resultSet.getString("user_id"));
//                    sysPeople.setDeptId(resultSet.getString("node_id"));
//                    SysDept sysDept = jdbcUtil.deptMapper.selectByOutId(resultSet.getString("node_id"));
//                    sysPeople.setDeptName(sysDept.getName());
//                    sysPeople.setCompanyId(sysDept.getParentId());
//                    sysPeople.setCompanyName(sysDept.getParent());
//                    sysPeople.setOrigin("场馆运营系统");
//                    sysPeople.setIsActive(resultSet.getBoolean("is_active"));
//                    sysPeople.setIsEffect(resultSet.getBoolean("is_effect"));
//                    jdbcUtil.peopleMapper.insert(sysPeople);
//                    resultSet1.close();
//                }
//            }
//            resultSet.close();
//            preparedStatement.close();
//            connection.close();
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//    }

//    public static void getArea(){
//        Connection connection;
//        String user = "gh001";  //数据库登录名
//        String password = "zjgh2023";  //密码
//
//        try{
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            connection = DriverManager.getConnection("jdbc:mysql://172.16.40.110:3306/property_service?useUnicode=true&characterEncoding=gbk", user, password);
//            String sql = "SELECT floormodel.name, precincts.name, stadiumsitemodel.name " +
//                    "FROM floormodel, precincts, stadiumsitemodel " +
//                    "WHERE stadiumsitemodel.floor_key_id = floormodel.id " +
//                    "AND stadiumsitemodel.precincts_id = precincts.id;";
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            ResultSet resultSet = preparedStatement.executeQuery();
//            while (resultSet.next()){
//                SysArea sysArea = new SysArea();
//                sysArea.setName(resultSet.getString("stadiumsitemodel.name"));
//                sysArea.setFloor(resultSet.getString("floormodel.name"));
//                sysArea.setArea(resultSet.getString("precincts.name"));
//                jdbcUtil.areaMapper.insert(sysArea);
//            }
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//    }

//    public static void getPeopleDepartment() {
//        Connection connection;
//        String user = "gh001";  //数据库登录名
//        String password = "zjgh2023";  //密码
//
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            connection = DriverManager.getConnection("jdbc:mysql://172.16.40.110:3306/basic_service?useUnicode=true&characterEncoding=gbk", user, password);
//            String sql1 = "SELECT * FROM user;";
//            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
//            ResultSet resultSet1 = preparedStatement1.executeQuery();
//
//            while (resultSet1.next()) {
//                String id = resultSet1.getString("id");
//                LambdaQueryWrapper<People> wrapper = Wrappers.lambdaQuery();
//                wrapper.eq(People::getOutId, id);
//                People people = jdbcUtil.peopleMapper.selectOne(wrapper);
//
//                people.setName(resultSet1.getString("name"));
//                people.setPhone(resultSet1.getString("mobile"));
//                people.setUsername(resultSet1.getString("name"));
//                people.setIsActive(resultSet1.getBoolean("is_active"));
//                jdbcUtil.peopleMapper.updateById(people);
//            }
//                resultSet1.close();
//                preparedStatement1.close();
//                connection.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    public static String transferFormat(String inTime) throws ParseException {
        SimpleDateFormat s1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat s2 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Date tempDate = null;
        String outTime = null;
        tempDate = s1.parse(inTime);
        outTime = s2.format(s2.parse(s1.format(tempDate)));
        return outTime;
    }
}
